package com.neo.service;

import java.io.FileNotFoundException;
import java.math.BigDecimal;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import com.github.stuxuhai.jpinyin.PinyinException;
import com.github.stuxuhai.jpinyin.PinyinHelper;
import com.neo.util.DateUtil;
import com.neo.util.DjsStringUtil;
import com.neo.util.RandomUserNameUtil;
import com.neo.util.ReadFromFile;


@Service
public class TransferService {

    protected static Logger logger= LoggerFactory.getLogger(TransferService.class); 
    
	@Autowired
    private JdbcTemplate jdbcTemplate;
	
	@Autowired
	private RandomUserNameUtil randomUserNameUtil;
	
	public void transfer() throws Exception {
		// inv_user  inv_user_account 迁移
		// 生成jx 请求文件(手动操作)
		// 迁移 jx_account_info 表 
		insert_jx_account_info();
		// 读取jx 结果文件  回写jx_account_info 表  更新 电子账号  (手动操作)                                                                                                   
       
		
		// 产品迁移 用sql8  迁移前得到最大id
		// 标的迁移用sql6
		// 资金方为：点金石、
		//产品为：老平台标的名字
		//资产方：亨元金融
		
		insert_dic_cp();
		
		updateBolanMobile();
		// 将已迁移的这些更新产品表的PRODUCT_CODE
		//update_orther_code();
		update_product_code();
		//	红包表迁移
		insert_redpacket();
	}

	public void insert_redpacket () {
		//点金石用户名,姓名,证件号码 ,手机号 ,点金石账户ID
		String sql = "INSERT INTO jx_red_packet (\n" + 
				"RED_PACKET_NAME,RED_PACKET_AMOUNT,TYPE ,INVEST_MIN_AMOUNT,\n" + 
				"STOCK,TOTAL,STATUS,CHANNEL_NO,\n" + 
				"REGISTER_FROM_PC,REGISTER_FROM_H5,REGISTER_FROM_IOS,\n" + 
				"REGISTER_FROM_ANDR,ALLOW_EDITING,COMMON_USE,INTO_ACCOUNT,\n" + 
				"GRANT_ROSTER \n" + 
				")\n" + 
				"SELECT \n" + 
				"RED_PACKET_NAME,RED_PACKET_AMOUNT,SECOND_TYPE,INVEST_MIN_AMOUNT,\n" + 
				"STOCK,TOTAL,STATUS,CHANNEL_NO,\n" + 
				"REGISTER_FROM_PC,REGISTER_FROM_H5,REGISTER_FROM_IOS,\n" + 
				"REGISTER_FROM_ANDR,ALLOW_EDITING,COMMON_USE,INTO_ACCOUNT,\n" + 
				"GRANT_ROSTER \n" + 
				"FROM inv_red_packet ";
		int i = jdbcTemplate.update(sql);
		logger.info("====="+i);
		
	}
	
	
	public void insert_jx_account_info() {
		//点金石用户名,姓名,证件号码 ,手机号 ,点金石账户ID
		String sql = "	INSERT INTO jx_account_info (username,NAME,id_no,mobile,ACC_ID)  " + 
				" SELECT a.username AS username ,NAME,a.IDENTITY_NO AS id_no ,mobile,b.id FROM inv_user a,inv_user_account b WHERE a.username=b.username" ; 
		int i = jdbcTemplate.update(sql);
	}
	
	public void insert_jx_inv_record(){
		List<String> errorProjectNo = new ArrayList<String>();
		
		StringBuilder select_sql = new StringBuilder("SELECT ");
		select_sql.append("ip.USERNAME AS USERNAME,ip.ACCOUNT_ID AS INV_ACCOUNT_ID,jp.BORROWER_ACCOUNT_ID AS BOR_ACCOUNT_ID,");
		select_sql.append("ip.PROJECT_NO AS PRODUCT_ID,pd.DIC_NAME AS PRODUCT_NAME,jpp.PROJECT_TYPE AS PRODUCT_TYPE,");
		select_sql.append("ip.INV_RATE AS YEAR_RATE,ip.EXPECTED_INCOME AS EXPECTED_INCOME,ip.INV_AMOUNT AS INV_AMOUNT,");
		select_sql.append("ip.INV_DAY AS INV_DAY,ip.CREATE_TIME AS INV_DATE,ip.INV_STATUS AS INV_STATUS,");
		select_sql.append("ip.INVEST_SOURCE AS INV_SOURCE,ip.INTEREST_SUN_STARTDATE AS INTEREST_START_DATE,ip.INV_OVER_DATE AS INTEREST_END_DATE,");
		select_sql.append("ip.INTEREST_SUN_NEXTDATE AS INTEREST_NEXT_DATE,ip.NEXT_SETTLEMENT_DAY AS SETTLEMENT_NEXT_DATE,ip.TAKE_BACK_DAYS AS TAKE_BACK_DAYS,");
		select_sql.append("ip.REMAINDER_DAYS AS REMAINDER_DAYS,ip.TAKE_BACK_PRINCIPAL AS TAKE_BACK_PRINCIPAL,ip.TAKE_BACK_INTEREST AS TAKE_BACK_INTEREST,");
		select_sql.append("ip.WAIT_BACK_PRINCIPAL AS WAIT_BACK_PRINCIPAL,ip.WAIT_BACK_INTEREST AS WAIT_BACK_INTEREST,ip.TRANSFER_AMOUNT AS TRANSFER_AMOUNT,");
		select_sql.append("ip.TRANSFER_TIME AS TRANSFER_TIME,ip.USER_COUPON_ID AS USER_COUPON_ID,ip.COUPON_RATE AS COUPON_RATE,");
		select_sql.append("ip.COUPON_EXPIRE_DATE AS COUPON_EXPIRE_DATE,ip.USER_REDPACKET_ID AS USER_REDPACKET_ID,ip.REDPACKET_AMOUNT AS REDPACKET_AMOUNT,");
		select_sql.append("ip.CREATE_TIME AS CREATE_TIME,ip.CREATE_USER AS CREATE_USER,ip.UPDATE_TIME AS UPDATE_TIME,");
		select_sql.append("ip.UPDATE_USER AS UPDATE_USER ");
		select_sql.append("FROM inv_user_invest_project ip ");
		select_sql.append("LEFT JOIN jx_invest_project jp ON ip.PROJECT_NO = jp.PROJECT_NO ");
		select_sql.append("LEFT JOIN jx_invest_project_product jpp ON jpp.id = jp.PRODUCT_ID ");
		select_sql.append("LEFT JOIN pu_dit_dic pd ON pd.DIC_CODE = jpp.PRODUCT_CODE");
		
		List<Map<String,Object>> old_invest_data = jdbcTemplate.queryForList(select_sql.toString(),new Object[]{}); 
		
		if(old_invest_data != null && old_invest_data.size() > 0){
			for(Map<String,Object> map : old_invest_data){
				String select_accId = "SELECT ACC_ID jx_account_info WHERE ACCOUNT_ID = '" + map.get("INV_ACCOUNT_ID") + "'";
				List<Map<String,Object>> invAccId = jdbcTemplate.queryForList(select_accId,new Object[]{});
				if(invAccId == null || invAccId.size() <= 0){
					System.out.println("标的号" + map.get("PROJECT_NO") + ",未查询到投资人ACC_ID");
					errorProjectNo.add(map.get("PROJECT_NO").toString());
					continue;
				}
				
				select_accId = "SELECT ACC_ID jx_account_info WHERE ACCOUNT_ID = '" + map.get("BOR_ACCOUNT_ID") + "'";
				List<Map<String,Object>> borAccId = jdbcTemplate.queryForList(select_accId,new Object[]{});
				if(borAccId == null || borAccId.size() <= 0){
					System.out.println("标的号" + map.get("PROJECT_NO") + ",未查询到借款人ACC_ID");
					errorProjectNo.add(map.get("PROJECT_NO").toString());
					continue;
				}
				StringBuilder insert_sql = new StringBuilder("INSERT INTO jx_inv_record ( ");
				insert_sql.append("USERNAME,INV_ACCOUNT_ID,INV_ACC_ID,BOR_ACCOUNT_ID,BOR_ACC_ID,");
				insert_sql.append("PRODUCT_ID,PRODUCT_NAME,PRODUCT_TYPE,");
				insert_sql.append("YEAR_RATE,EXPECTED_INCOME,INV_AMOUNT,");
				insert_sql.append("INV_DAY,INV_DATE,INV_STATUS,");
				insert_sql.append("INV_SOURCE,INTEREST_START_DATE,INTEREST_END_DATE,");
				insert_sql.append("INTEREST_NEXT_DATE,SETTLEMENT_NEXT_DATE,TAKE_BACK_DAYS,");
				insert_sql.append("REMAINDER_DAYS,TAKE_BACK_PRINCIPAL,TAKE_BACK_INTEREST,");
				insert_sql.append("WAIT_BACK_PRINCIPAL,WAIT_BACK_INTEREST,TRANSFER_AMOUNT,");
				insert_sql.append("TRANSFER_TIME,USER_COUPON_ID,COUPON_RATE,");
				insert_sql.append("COUPON_EXPIRE_DATE,USER_REDPACKET_ID,REDPACKET_AMOUNT,");
				insert_sql.append("CREATE_TIME,CREATE_USER,UPDATE_TIME,");
				insert_sql.append("UPDATE_USER) VALUE(");
				insert_sql.append("'" + map.get("USERNAME").toString() + "',");
				insert_sql.append("'" + map.get("INV_ACCOUNT_ID") + "',");
				insert_sql.append("'" + invAccId.get(0).get("ACC_ID") + "',");
				insert_sql.append("'" + map.get("BOR_ACCOUNT_ID") + "',");
				insert_sql.append("'" + borAccId.get(0).get("ACC_ID") + "',");
				insert_sql.append("'" + map.get("PRODUCT_ID") + "',");
				insert_sql.append("'" + map.get("PRODUCT_NAME") + "',");
				insert_sql.append("'" + map.get("PRODUCT_TYPE") + "',");
				insert_sql.append("'" + map.get("YEAR_RATE").toString() + "',");
				insert_sql.append("'" + map.get("EXPECTED_INCOME") + "',");
				insert_sql.append("'" + map.get("INV_AMOUNT") + "',");
				insert_sql.append("'" + map.get("INV_DAY") + "',");
				insert_sql.append("'" + map.get("INV_DATE") + "',");
				insert_sql.append("'" + map.get("INV_STATUS") + "',");
				insert_sql.append("'" + map.get("INV_SOURCE") + "',");
				insert_sql.append("'" + map.get("INTEREST_START_DATE") + "',");
				insert_sql.append("'" + map.get("INTEREST_END_DATE") + "',");
				insert_sql.append("'" + map.get("INTEREST_NEXT_DATE") + "',");
				insert_sql.append("'" + map.get("SETTLEMENT_NEXT_DATE") + "',");
				insert_sql.append("'" + map.get("TAKE_BACK_DAYS") + "',");
				insert_sql.append("'" + map.get("REMAINDER_DAYS") + "',");
				insert_sql.append("'" + map.get("TAKE_BACK_PRINCIPAL") + "',");
				insert_sql.append("'" + map.get("TAKE_BACK_INTEREST") + "',");
				insert_sql.append("'" + map.get("WAIT_BACK_PRINCIPAL") + "',");
				insert_sql.append("'" + map.get("WAIT_BACK_INTEREST") + "',");
				insert_sql.append("'" + map.get("TRANSFER_AMOUNT") + "',");
				insert_sql.append("'" + map.get("TRANSFER_TIME") + "',");
				insert_sql.append("'" + map.get("USER_COUPON_ID") + "',");
				insert_sql.append("'" + map.get("COUPON_RATE") + "',");
				insert_sql.append("'" + map.get("COUPON_EXPIRE_DATE") + "',");
				insert_sql.append("'" + map.get("USER_REDPACKET_ID") + "',");
				insert_sql.append("'" + map.get("COUPON_RATE") + "',");
				insert_sql.append("'" + map.get("COUPON_EXPIRE_DATE") + "',");
				insert_sql.append("'" + map.get("USER_REDPACKET_ID") + "',");
				insert_sql.append("'" + map.get("REDPACKET_AMOUNT") + "',");
				insert_sql.append("'" + map.get("CREATE_TIME") + "',");
				insert_sql.append("'" + map.get("CREATE_USER") + "',");
				insert_sql.append("'" + map.get("UPDATE_TIME") + "',");
				insert_sql.append("'" + map.get("UPDATE_USER") + "')");
				
				int result = jdbcTemplate.update(insert_sql.toString());
				
				if(result <= 0){
					errorProjectNo.add(map.get("PROJECT_NO").toString());
					
				}
			}
		}
		
	}
	


	
	//插入数据字典  资产方
//	public void insert_dic_zcf() {
//		List<Map<String, Object>> list = jdbcTemplate.queryForList(" SELECT trade_name FROM inv_bond_project WHERE trade_name NOT IN (SELECT dic_name FROM pu_dit_dic WHERE dty_code='BDZC') GROUP BY trade_name " , new Object[]{});
//		for (Map map : list) {
//			String trade_name = (String )map.get("trade_name");
//			String py = null;
//			try {
//				py = PinyinHelper.getShortPinyin(trade_name).toUpperCase();
//			} catch (PinyinException e) {
//				e.printStackTrace();
//			}
//			
//			String sql = "INSERT INTO pu_dit_dic ( DTY_CODE, DIC_CODE, DIC_NAME, DIC_DESC,enabled ) "
//					+ "	VALUES	(	?,	?, 	? ,?,?);";
//			int i = jdbcTemplate.update(sql,"BDZC",py,trade_name,trade_name,1);
//		}
//	}
//	//插入数据字典 资金方
//	public void insert_dic_zjf() {
//		List<Map<String, Object>> list = jdbcTemplate.queryForList(" SELECT partner FROM inv_bond_project WHERE partner NOT IN (SELECT dic_name FROM pu_dit_dic WHERE dty_code='BDZJ') GROUP BY partner " , new Object[]{});
//		for (Map map : list) {
//			String partner = (String )map.get("partner");
//			String py = null;
//			try {
//				py = PinyinHelper.getShortPinyin(partner).toUpperCase();
//			} catch (PinyinException e) {
//				e.printStackTrace();
//			}
//			
//			String sql = "INSERT INTO pu_dit_dic ( DTY_CODE, DIC_CODE, DIC_NAME, DIC_DESC,enabled ) "
//					+ "	VALUES	(	?,	?, 	? ,?,?);";
//			int i = jdbcTemplate.update(sql,"BDZJ",py,partner,partner,1);
//		}
//	}
	//插入数据字典  产品
	public void insert_dic_cp() {
		List<Map<String, Object>> list = jdbcTemplate.queryForList(" SELECT project_name FROM inv_invest_project WHERE project_name NOT IN (SELECT dic_name FROM pu_dit_dic WHERE dty_code='BDCP') GROUP BY project_name " , new Object[]{});
		for (Map map : list) {
			String project_name = (String )map.get("project_name");
			String py = null;
			try {
				py = PinyinHelper.getShortPinyin(project_name).toUpperCase();
			} catch (PinyinException e) {
				e.printStackTrace();
			}
			
			String sql = "INSERT INTO pu_dit_dic ( DTY_CODE, DIC_CODE, DIC_NAME, DIC_DESC,enabled ) "
					+ "	VALUES	(	?,	?, 	? ,?,?);";
			int i = jdbcTemplate.update(sql,"BDCP",py,project_name,project_name,1);
		}
	}
//	//更新产品表的code 字段
//	public void update_orther_code() {
//		List<Map<String, Object>> list = jdbcTemplate.queryForList("\n" + 
//				"SELECT a.project_no ,PROJECT_NAME,e.dic_code AS ecode ,TRADE_NAME,d.dic_code AS dcode,PARTNER,c.dic_code AS ccode FROM  inv_invest_project a ,inv_bond_project b ,\n" + 
//				"(SELECT * FROM pu_dit_dic WHERE dty_code='BDZJ') c,\n" + 
//				"(SELECT * FROM pu_dit_dic WHERE dty_code='BDZC') d,\n" + 
//				"(SELECT * FROM pu_dit_dic WHERE dty_code='BDCP') e \n" + 
//				"WHERE a.project_no=b.project_no \n" + 
//				"AND c.dic_name=PARTNER\n" + 
//				"AND d.dic_name=TRADE_NAME\n" + 
//				"AND e.dic_name=PROJECT_NAME " , new Object[]{});
//		for (Map map : list) {
//			String project_no = (String )map.get("project_no");
//			String ecode = (String )map.get("ecode");
//			String dcode = (String )map.get("dcode");
//			String ccode = (String )map.get("ccode");
//			String sql = "update jx_invest_project_product set PRODUCT_CODE=?,ASSET_CODE=?,CAPITAL_CODE=? where PROJECT_NO= ?";
//			int i = jdbcTemplate.update(sql,ecode,dcode,ccode,project_no);
//		}
//	}

	//更新产品表的 产品code 字段
	public void update_product_code() {
		List<Map<String, Object>> list = jdbcTemplate.queryForList("\n" + 
				"SELECT a.project_no,PROJECT_NAME,e.dic_code AS ecode  FROM inv_invest_project a ,\n" + 
				"(SELECT * FROM pu_dit_dic WHERE dty_code='BDCP') e \n" + 
				"WHERE  e.dic_name = PROJECT_NAME\n" + 
				" " , new Object[]{});
		for (Map map : list) {
			String project_no = (String )map.get("project_no");
			String ecode = (String )map.get("ecode");
			List<Map<String, Object>> isExist =  jdbcTemplate.queryForList("select * from jx_invest_project_product where PRODUCT_CODE=?  ",  new Object[]{ecode});
			if (isExist.size()==0) {
				String sql = "update jx_invest_project_product set PRODUCT_CODE=? where PROJECT_NO= ?";
				int i = jdbcTemplate.update(sql,ecode,project_no);
			}
		}
	}
	
	//更新产品表的code 字段
	public void update_balance() {
		List<Map<String, Object>> list = jdbcTemplate.queryForList(
				"select balance,batch_to_time from  inv_user_account  a where a.username in (select username from  jx_account_info where account_id is not null ) " + 
				" " , new Object[]{});
		for (Map map : list) {
			BigDecimal balance = (BigDecimal )map.get("balance");

			BigDecimal sum =  jdbcTemplate.queryForObject(
					"select balance,batch_to_time from  inv_user_account  a where a.username in (select username from  jx_account_info where account_id is not null ) " + 
					" " , new Object[]{},BigDecimal.class);
			
			
		}
	}
	
	//读取刘峰给的借款人信息excel,更新手机号到用户表
	public void updateBolanMobile() throws FileNotFoundException{
		List listCsv = ReadFromFile.readFileByLines(DjsStringUtil.IMPORT_FILE_PATH);
    	for (int i = 1; i < listCsv.size(); i++) {
			String[] s = (String[])listCsv.get(i);
			String idNo = s[3];
			String mobileNo = s[4];
	    	String sql =" UPDATE inv_user SET  mobile=? where identity_no =? ;";
	    	int j = jdbcTemplate.update(sql,mobileNo,idNo);
		}
	}

	//临时生成删除表sql 
	public void generalSql() throws FileNotFoundException{
		List listCsv = ReadFromFile.readFileByLines("D:/点金石/新债权信息/需要代偿名单.csv");
    	for (int i = 2; i < listCsv.size(); i++) {
			String[] s = (String[])listCsv.get(i);
			String PROJECT_NO = s[0];
	    	String sql =" delete from jx_invest_project where PROJECT_NO ='"+PROJECT_NO+"';";
	    	System.out.println(sql);
	    	//int j = jdbcTemplate.update(sql,PROJECT_NO);
		}
	}
	public static void main(String[] args) throws Exception {
		System.out.println( PinyinHelper.getShortPinyin("嗨秒贷").toUpperCase());
	}

}
